﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace BicycleTrafficSimulator.DataModel
{
    public class DataBase
    {
        SQLiteConnection dbConnection;

        public DataBase()
        {

            dbConnection = new SQLiteConnection("Data Source=Users.sqlite;Version=3; FailIfMissing = True"); //connect
            try
            {
                dbConnection.Open();
                // this.ReadUsers();
                // this.EmptyTable();
                this.ReadUsers();
            }
            catch (SQLiteException e)
            {
                System.Console.WriteLine("DB does not exist");
                SQLiteConnection.CreateFile("Users.sqlite"); //create
                dbConnection = new SQLiteConnection("Data Source=Users.sqlite;Version=3"); //connect
                dbConnection.Open();

                //creat table 
                string sql = "create table users (first_name varchar(20), age int, score int)";
                SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
                command.ExecuteNonQuery();
            }
        }

        public void AddUser(User user)
        {

            string sqlSelect = "select * from users where first_name = @name";
            SQLiteCommand commandRead = new SQLiteCommand(sqlSelect, dbConnection);
            commandRead.Parameters.AddWithValue("@name", user.Name);
            SQLiteDataReader reader = commandRead.ExecuteReader();
            Boolean exists = false;
            while (reader.Read())
                exists = true;
            if (exists)
            {
                Console.WriteLine("User already exists");
            }
            else
            {
                string sql = "insert into users (first_name, age, score) values (@name, @age, 0)";
                SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
                command.Parameters.AddWithValue("@name", user.Name);
                command.Parameters.AddWithValue("@age", user.Age);
                command.ExecuteNonQuery();
            }
        }

        public void UpdateUserAge(User user)
        {

            string sql = "update users set age = @age where first_name = @name";
            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            command.Parameters.AddWithValue("@age", user.Age);
            command.Parameters.AddWithValue("@name", user.Name);
            command.ExecuteNonQuery();
        }

        public void UpdateUserScore(User user, int score)
        {

            string sql = "update users set score = @score where first_name = @name";
            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            command.Parameters.AddWithValue("@score", score);
            command.Parameters.AddWithValue("@name", user.Name);
            command.ExecuteNonQuery();

        }

        public void ReadUsers()
        {

            string sqlSelect = "select * from users order by score desc";
            SQLiteCommand commandRead = new SQLiteCommand(sqlSelect, dbConnection);
            SQLiteDataReader reader = commandRead.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("Name: " + reader["first_name"] + "\tAge: " + reader["age"] + "\tScore: " + reader["score"]);
        }

        public void EmptyTable()
        {

            string sql = "delete from users";
            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            command.ExecuteNonQuery();
        }

        public Boolean existsUser(User user)
        {
            string sqlSelect = "select * from users where first_name = @name";
            SQLiteCommand commandRead = new SQLiteCommand(sqlSelect, dbConnection);
            commandRead.Parameters.AddWithValue("@name", user.Name);
            SQLiteDataReader reader = commandRead.ExecuteReader();
            Boolean exists = false;
            while (reader.Read())
                exists = true;
            if (exists)
                return (true);
            else
                return (false);
        }

        public User readUser(User user)
        {
            string sqlSelect = "select * from users where first_name = @name";
            SQLiteCommand commandRead = new SQLiteCommand(sqlSelect, dbConnection);
            commandRead.Parameters.AddWithValue("@name", user.Name);
            SQLiteDataReader reader = commandRead.ExecuteReader();

            while (reader.Read())
            {
                user.Age = (Int32)reader["age"];
                user.Score.Value = (Int32)reader["score"];
            }
            return user;
        }
    }
}
